const Mysql = require('./mysql.js'),
    sql = new Mysql();
class Api{
    //根据用户名得到用户数量
    getUserNumByName(username,callback){
        sql.get("SELECT COUNT(1) AS num FROM userinfo WHERE UserName = ?",[username],(err,result) => {
            callback(result)
        })
    };
    //根据用户名得到用户信息
    getUserByUserName(username, callback){
        sql.get("SELECT * FROM userinfo WHERE UserName = ?",[username],(err,result) => {
            callback(result)
        })
    };
    //得到用户数量(后台)
    getUserNumber(callback){
        sql.get("SELECT COUNT(1) AS num FROM userinfo",(err,result) => {
            callback(result)
        })
    };
    //导航查询(后台)
    getNavInfo(callback){
        sql.get("SELECT * FROM nav ORDER BY Id",(err,result) => {
            callback(result);
        })
    };
    //文章 查询 DESC:新>旧
    getArticle(callback){
        sql.get("SELECT * FROM article ORDER BY id DESC",(err,result) => {
            callback(result)
        })
    };
    //根据tag查询文章
    getArticleByTag(tag,callback){
        sql.get("SELECT * FROM article WHERE tag=? GROUP BY id DESC",[tag],(err,result) => {
            callback(result)
        })
    };
    //根据id 删除文章(后台)
    deleteArticleById(id,callback){
        sql.get("delete from article where id=?",[id],(err,result) => {
            callback(result)
        })
    };
    //根据id 查询文章
    getArticleById(id,callback){ // 关联查询文章表和评论表
        // SELECT * FROM article JOIN comment on article.id = comment.articleid WHERE articleid = ?;
        sql.get("SELECT * FROM article WHERE id = ?",[id],(err,result) => {
            callback(result)
        })
    };
    //查询最新 N 篇文章
    getNewArticle(articleNum,callback){
        sql.get("SELECT * FROM article ORDER BY id DESC  LIMIT ?,?",[articleNum.start,articleNum.end],(err,result) => {
            callback(result)
        })
    };
    //查询文章评论数 ()
    getArticleCommentNumber(id,callback){
        sql.get("SELECT COUNT(1) AS num FROM comment WHERE articleid = ?",[id],(err,result) => {
            callback(result)
        })
    };
    //更新评论数量
    upDataArticleCommentNumber(commentnum,callback){
        sql.get("update article set commentnum = ? where id = ?;",[commentnum.num,commentnum.commentId],(err,result) => {
            callback(result)
        })
    };
    //根据文章id查询评论
    getArticleCommentById(id,callback){
        sql.get("SELECT * FROM comment WHERE articleid = ? GROUP BY id DESC",[id],(err,result) => {
            callback(result)
        })
    };
    //根据id 删除评论
    deleteCommentById(id,callback){
        sql.get("delete from comment where id=?",[id],(err,result) => {
            callback(result)
        })
    };
    //根据文章id 删除评论 (后台)
    deleteCommentByArticleId(id,callback){
        sql.get("delete from comment where articleid =?",[id],(err,result) => {
            callback(result)
        })
    };
}
module.exports = Api;

/*
 * SELECT * FROM `userinfo`  JOIN `nav` USING (id) WHERE UserName = 123 // 根据id关联查询
 * SELECT * FROM `article` JOIN `comment` on article.id = comment.articleid WHERE articleid = 89;
 * */